package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import persistence.exception.DAOException;
import persistence.util.JdbcUtil;
import business.validation.ValidationException;


public class SubscriptionDAOJdbcImpl extends JdbcDAOBase implements SubscriptionDAO {
	private LibraryLocationDAO libraryLocationDAO;
	private SupplierDAO supplierDAO;
	private CostUnitDAO costUnitDAO;
	private PurchaserDAO purchaserDAO;
	private PeriodicalDAO periodicalDAO;
	private BindingDetailsDAO bindingDetailsDAO;
	private PredefinedEntryDAO predefinedEntryDAO;

	public SubscriptionDAOJdbcImpl(){
		super();
	}	

	/**
	 * get all subscriptions by title
	 * 
	 * @param String that the title of the subscriptions periodical starts with
	 */
	@Override
	public List<Subscription> getAllSubscriptionsByTitle(String title) throws DAOException{
		
		System.out.println("------------------ CALLING getAllSubscriptionsByTitle() ---");
		
		Connection con = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		
		List<Subscription> subscriptions=null;
		Subscription subscription = null;
		LibraryLocation libraryLocation = null;
		Supplier supplier = null;
		CostUnit costUnit = null;
		Purchaser purchaser = null;
		Periodical periodical = null;
		BindingDetails bindingDetails = null;
		PredefinedEntry status = null;
		PredefinedEntry kindOfAcquisition = null;
		PredefinedEntry currency = null;
		PredefinedEntry kindOfStorage = null;
		
		String sql = "SELECT a.id, a.abo_nr, a.abo_status, a.preis, a.preis_vorjahr, a.abo_startdatum," +
			/*7-11*/    " a.abo_enddatum, a.interner_standort, a.bezahlt_bis, a.kuendigungsfrist, a.online_in_aleph," +
			/*12-15*/	" a.signatur, a.bestellt_am, a.barcode, a.eingangsdatum_aktuelles_heft," +
			/*16-21*/	" a.erwerbungsart, a.anmerkungen, a.anmerkungen_online, a.waehrung, a.aufbewahren, a.bibliotheksstandorte_id," +
			/*22-27*/	" a.lieferanten_id, a.kostenstellen_id, a.besteller_id, a.zeitschriften_id, a.bindedetails_id, a.mahnfrequenz_in_tagen" + 
						" FROM abos a INNER JOIN zeitschriften z ON a.zeitschriften_id=z.id"+
						" WHERE upper(z.titel) LIKE ? ORDER BY z.titel ASC";
		try {
			con = getConProvider().getConnection();
			if (con == null) {
				System.out.println("----------------------- CONNECTION IS NULL");
			}
			stmt = con.prepareStatement(sql);
			stmt.setString(1, (title.length() > 0 ? title.toUpperCase() : "") +"%");
			rs=stmt.executeQuery();				
			subscriptions = new ArrayList<Subscription>();
			
			while(rs.next()) {
				System.out.println("---------------- SQL delivers RESULT -----------");
				//create Subscription from record, use helper methods to create dependent objects
				//add Subscription to list
//				Subscription sub= new Subscription(rs.getInt(1));
//				sub.setPeriodical(new Periodical(rs.getInt(26), rs.getString(27), rs.getString(28), rs.getString(29),
//						rs.getString(29), rs.getString(30), rs.getString(31), rs.getString(32), rs.getString(33), 
//						rs.getString(34), rs.getString(35), rs.getBoolean(36), rs.getBoolean(37),
//						rs.getString(38)));
//				System.out.println(sub.getPeriodical().getTitle());
//				subscriptions.add(sub);
				libraryLocation = libraryLocationDAO.find(rs.getInt("bibliotheksstandorte_id"));
				supplier = supplierDAO.find(rs.getInt("lieferanten_id"));
				costUnit = costUnitDAO.find(rs.getInt("kostenstellen_id"));
				purchaser = purchaserDAO.find(rs.getInt("besteller_id"));
				periodical = periodicalDAO.find(rs.getInt("zeitschriften_id"));
				bindingDetails = bindingDetailsDAO.find(rs.getInt("bindedetails_id"));
				status = predefinedEntryDAO.find(rs.getInt("abo_status"));
				kindOfAcquisition = predefinedEntryDAO.find(rs.getInt("erwerbungsart"));
				currency = predefinedEntryDAO.find(rs.getInt("waehrung"));
				kindOfStorage = predefinedEntryDAO.find(rs.getInt("aufbewahren"));
				
				subscription = new Subscription(rs.getInt("id"), rs.getString("abo_nr"), status, rs.getFloat("preis"), rs.getFloat("preis_vorjahr"), rs.getDate("abo_startdatum"),
						rs.getDate("abo_enddatum"), rs.getString("interner_standort"),rs.getDate("bezahlt_bis"), rs.getString("kuendigungsfrist"), rs.getBoolean("online_in_aleph"),
						rs.getString("signatur"), rs.getDate("bestellt_am"), rs.getString("barcode"), rs.getDate("eingangsdatum_aktuelles_heft"),
						kindOfAcquisition, rs.getString("anmerkungen"), rs.getString("anmerkungen_online"), currency, kindOfStorage, libraryLocation,
						supplier, costUnit, purchaser, periodical, bindingDetails, rs.getInt("mahnfrequenz_in_tagen"));
				subscriptions.add(subscription);
				System.out.println("----------------- Added Subscription");
			}
		}
		catch(SQLException se) {
			throw new DAOException("Problem when attempting to get all Subscriptions with title like: " + title + "%", se);
		}
		finally {
			JdbcUtil.cleanup(con, stmt, rs);
			System.out.println("-------------- leaving DAOImpl");
		}
		return subscriptions;
	}	
	
	/**
	 * get all the subscriptions in ascending order	 * 
	 * 
	 */
	public List<Subscription> getAllSubscriptionsAsc() throws DAOException{
		return this.getAllSubscriptionsByTitle("");
	}

	@Override
	public List<Subscription> getAllSubscriptionsForLocation(
			LibraryLocation location) throws DAOException {
		String sql = "SELECT a.id, a.abo_nr, a.abo_status, a.preis, a.preis_vorjahr, a.abo_startdatum," +
		/*7-11*/    " a.abo_enddatum, a.interner_standort, a.bezahlt_bis, a.kuendigungsfrist, a.online_in_aleph," +
		/*12-15*/	" a.signatur, a.bestellt_am, a.barcode, a.eingangsdatum_aktuelles_heft," +
		/*16-20*/	" a.erwerbungsart, a.anmerkungen, a.anmerkungen_online, a.waehrung, a.aufbewahren," +
		/*21-26*/	" a.lieferanten_id, a.kostenstellen_id, a.besteller_id, a.zeitschriften_id, a.bindedetails_id, a.mahnfrequenz_in_tagen" + 
					" FROM abos a INNER JOIN zeitschriften z ON a.zeitschriften_id=z.id"+
					" WHERE a.bibliotheksstandorte_id = ? ORDER BY z.titel ASC";
		
		List<Subscription> subscriptions = new ArrayList<Subscription>();
		Subscription currentSubscription = null;
		PredefinedEntry status = null;
		PredefinedEntry kindOfAcquisition = null;
		PredefinedEntry currency = null;
		PredefinedEntry kindOfStorage = null;
		Supplier supplier = null;
		CostUnit costUnit = null;
		Purchaser purchaser = null;
		Periodical periodical = null;
		BindingDetails bindingDetails = null;		
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		
		try {
			con = getConProvider().getConnection();
			prepStmt = con.prepareStatement(sql);
			prepStmt.setInt(1, location.getId());
			rs = prepStmt.executeQuery();
			
			while (rs.next()) {
				status = predefinedEntryDAO.find(rs.getInt("abo_status"));
				kindOfAcquisition = predefinedEntryDAO.find(rs.getInt("erwerbungsart"));
				currency = predefinedEntryDAO.find(rs.getInt("waehrung"));
				kindOfStorage = predefinedEntryDAO.find(rs.getInt("aufbewahren"));
				supplier = supplierDAO.find(rs.getInt("lieferanten_id"));
				costUnit = costUnitDAO.find(rs.getInt("kostenstellen_id"));
				purchaser = purchaserDAO.find(rs.getInt("besteller_id"));
				periodical = periodicalDAO.find(rs.getInt("zeitschriften_id"));
				bindingDetails = bindingDetailsDAO.find(rs.getInt("bindedetails_id"));
				
				currentSubscription = new Subscription(rs.getInt("id"), rs.getString("abo_nr"), status, rs.getFloat("preis"), rs.getFloat("preis_vorjahr"), rs.getDate("abo_startdatum"),
						rs.getDate("abo_enddatum"), rs.getString("interner_standort"),rs.getDate("bezahlt_bis"), rs.getString("kuendigungsfrist"), rs.getBoolean("online_in_aleph"),
						rs.getString("signatur"), rs.getDate("bestellt_am"), rs.getString("barcode"), rs.getDate("eingangsdatum_aktuelles_heft"),
						kindOfAcquisition, rs.getString("anmerkungen"), rs.getString("anmerkungen_online"), currency, kindOfStorage, location,
						supplier, costUnit, purchaser, periodical, bindingDetails, rs.getInt("mahnfrequenz_in_tagen"));
				subscriptions.add(currentSubscription);
			}
		}
		catch(SQLException se) {
			throw new DAOException("Problem when attempting to get all Subscriptions for location: " + location, se);
		}
		finally {
			JdbcUtil.cleanup(con, prepStmt, rs);
		}
		return subscriptions;
	}

	@Override
	public Subscription findSubscription(int id) throws DAOException{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		//entities
		Subscription subscription = null;
		LibraryLocation libraryLocation = null;
		Supplier supplier = null;
		CostUnit costUnit = null;
		Purchaser purchaser = null;
		Periodical periodical = null;
		BindingDetails bindingDetails = null;
		PredefinedEntry status = null;
		PredefinedEntry kindOfAcquisition = null;
		PredefinedEntry currency = null;
		PredefinedEntry kindOfStorage = null;	
		
		String sql = "SELECT a.id, a.abo_nr, a.abo_status, a.preis, a.preis_vorjahr, a.abo_startdatum," +
			/*7-11*/	" a.abo_enddatum, a.interner_standort, a.bezahlt_bis, a.kuendigungsfrist, a.online_in_aleph," +
			/*12-15*/	" a.signatur, a.bestellt_am, a.barcode, a.eingangsdatum_aktuelles_heft," +
			/*16-21*/	" a.erwerbungsart, a.anmerkungen, a.anmerkungen_online, a.waehrung, a.aufbewahren, a.bibliotheksstandorte_id," +
			/*22-28*/	" a.lieferanten_id, a.kostenstellen_id, a.besteller_id, a.zeitschriften_id, a.mahnfrequenz_in_tagen, z.titel, z.verlag" +
						" FROM abos a INNER JOIN zeitschriften z ON a.zeitschriften_id=z.id WHERE a.id=?";
		
		try{
			con = getConProvider().getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			
			if (rs.next()) {	
				libraryLocation = libraryLocationDAO.find(rs.getInt(21));
				supplier = supplierDAO.find(rs.getInt(22));
				//Problem: no id is stored in the DB
				System.out.println("ATTEMPTING TO FIND COST UNIT BASED IN ID=" + rs.getInt(23) + " returned FROM query AT abos >>>><<<<");
				costUnit = costUnitDAO.find(rs.getInt(23));
				purchaser = purchaserDAO.find(rs.getInt(24));
				periodical = periodicalDAO.find(rs.getInt(25));
				bindingDetails = bindingDetailsDAO.find(rs.getInt(1));
				status = predefinedEntryDAO.find(rs.getInt(3));
				kindOfAcquisition = predefinedEntryDAO.find(rs.getInt(16));
				currency = predefinedEntryDAO.find(rs.getInt(19));
				kindOfStorage = predefinedEntryDAO.find(rs.getInt(20));
				
				subscription = new Subscription(rs.getInt("id"), rs.getString("abo_nr"), status, rs.getFloat("preis"), rs.getFloat("preis_vorjahr"), rs.getDate("abo_startdatum"),
						rs.getDate("abo_enddatum"), rs.getString("interner_standort"),rs.getDate("bezahlt_bis"), rs.getString("kuendigungsfrist"), rs.getBoolean("online_in_aleph"),
						rs.getString("signatur"), rs.getDate("bestellt_am"), rs.getString("barcode"), rs.getDate("eingangsdatum_aktuelles_heft"),
						kindOfAcquisition, rs.getString("anmerkungen"), rs.getString("anmerkungen_online"), currency, kindOfStorage, libraryLocation,
						supplier, costUnit, purchaser, periodical, bindingDetails, rs.getInt("mahnfrequenz_in_tagen"));				
			}
		}
		catch(SQLException se) {
			throw new DAOException("Error when attempting to find Subscription with id: " + id , se);
		}
		finally {
			JdbcUtil.cleanup(con, stmt, rs);
		}
		return subscription;
	}

	@Override
	public Subscription updateSubscription(Subscription s) throws DAOException{
		System.out.println("ENTERING updateSubscription, CostUnit with id = ----------------------- " + s.getCostUnit().getId());
		String sql = "UPDATE abos SET abo_nr = ?, abo_status = ?, preis = ?, preis_vorjahr = ?, abo_startdatum = ?," +
		/*6-10*/	 " abo_enddatum = ?, interner_standort = ?, bezahlt_bis = ?, kuendigungsfrist = ?, online_in_aleph = ?," +
		/*11-14*/	 " signatur = ?, bestellt_am = ?, barcode = ?, eingangsdatum_aktuelles_heft = ?," +
		/*15-18*/	 " erwerbungsart = ?, anmerkungen = ?, anmerkungen_online = ?, waehrung = ?," +
		/*19-23*/	 " aufbewahren = ?, bibliotheksstandorte_id = ?, lieferanten_id = ?, kostenstellen_id = ?, besteller_id = ?," +
		/*24-26*/	 " zeitschriften_id = ?, bindedetails_id = ?, mahnfrequenz_in_tagen = ? WHERE id = ?";
		
		int subscriptionId = s.getId();
		Connection con = null;
		PreparedStatement prepStmt = null;
		Subscription retVal = null;
		
		try {
			
			con = getConProvider().getConnection();
			prepStmt = con.prepareStatement(sql);
//			JdbcUtil.setParam(prepStmt, s.getSubscriptionNo(), 1, Types.VARCHAR);
//			if (s.getStatus() != null)
//				prepStmt.setInt(2, s.getStatus().getEntryId());
//			else
//				JdbcUtil.setParam(prepStmt, null, 2, Types.INTEGER);
//			JdbcUtil.setParam(prepStmt, s.getCurrentPrice(), 3, Types.FLOAT);
//			JdbcUtil.setParam(prepStmt, s.getPreviousPrice(), 4, Types.FLOAT);
//			if (s.getStartDate() != null)
//				prepStmt.setDate(5, new java.sql.Date(s.getStartDate().getTime()));
//			else
//				JdbcUtil.setParam(prepStmt, null, 5, Types.DATE);
//			if (s.getEndDate() != null)
//				prepStmt.setDate(6, new java.sql.Date(s.getEndDate().getTime()));
//			else
//				JdbcUtil.setParam(prepStmt, null, 6, Types.DATE);
//			prepStmt.setString(7, s.getInternalLocation());
//			if (s.getPaidUntil() != null)
//				prepStmt.setDate(8, new java.sql.Date(s.getPaidUntil().getTime()));
//			else
//				JdbcUtil.setParam(prepStmt, null, 8, Types.DATE);
//			JdbcUtil.setParam(prepStmt, s.getCancellationPeriod(), 9, Types.VARCHAR);
//			JdbcUtil.setParam(prepStmt, s.isOnlineInAleph(), 10, Types.BOOLEAN);
//			JdbcUtil.setParam(prepStmt, s.getSignature(), 11, Types.VARCHAR);
//			if (s.getOrderingDate() != null)
//				prepStmt.setDate(12, new java.sql.Date(s.getOrderingDate().getTime()));
//			else
//				JdbcUtil.setParam(prepStmt, null, 12, Types.DATE);
//			JdbcUtil.setParam(prepStmt, s.getBarcode(), 13, Types.VARCHAR);
//			if (s.getCurrentIssueDate() != null)
//				prepStmt.setDate(14, new java.sql.Date(s.getCurrentIssueDate().getTime()));
//			else
//				JdbcUtil.setParam(prepStmt, null, 14, Types.DATE);
//			if (s.getKindOfAcquisition() != null)
//				prepStmt.setInt(15, s.getKindOfAcquisition().getEntryId());
//			else
//				JdbcUtil.setParam(prepStmt, null, 15, Types.INTEGER);
//			JdbcUtil.setParam(prepStmt, s.getNotes(), 16, Types.VARCHAR);
//			JdbcUtil.setParam(prepStmt, s.getOnlineNotes(), 17, Types.VARCHAR);
//			if (s.getCurrency() != null)
//				prepStmt.setInt(18, s.getCurrency().getEntryId());
//			else
//				JdbcUtil.setParam(prepStmt, null, 18, Types.INTEGER);
//			if (s.getKindOfStorage() != null)
//				prepStmt.setInt(19, s.getKindOfStorage().getEntryId());
//			else
//				JdbcUtil.setParam(prepStmt, null, 19, Types.INTEGER);
//			if (s.getLocation() != null)
//				prepStmt.setInt(20, s.getLocation().getId());
//			else
//				JdbcUtil.setParam(prepStmt, null, 20, Types.INTEGER);
//			if (s.getSupplier() != null)
//				prepStmt.setInt(21, s.getSupplier().getId());
//			else
//				JdbcUtil.setParam(prepStmt, null, 21, Types.INTEGER);
//			if (s.getCostUnit() != null) {
//				System.out.println("Before setting CostUnit: id=" + s.getCostUnit().getId());
//				prepStmt.setInt(22, s.getCostUnit().getId());
//			}
//			else
//				System.out.println("---------------- CostUnit is null !!! ----");
//				JdbcUtil.setParam(prepStmt, null, 22, Types.INTEGER);
//			if (s.getPurchaser() != null)	
//				prepStmt.setInt(23, s.getPurchaser().getId());
//			else
//				JdbcUtil.setParam(prepStmt, null, 23, Types.INTEGER);
//			if (s.getPeriodical() != null)
//				prepStmt.setInt(24, s.getPeriodical().getId());
//			else
//				JdbcUtil.setParam(prepStmt, null, 24, Types.INTEGER);
//			if (s.getBookbindingDetails() != null)
//				prepStmt.setInt(25, s.getBookbindingDetails().getId());
//			else
//				JdbcUtil.setParam(prepStmt, null, 25, Types.INTEGER);
//			JdbcUtil.setParam(prepStmt, s.getReminderFrequency(), 26, Types.INTEGER);
			setUpdateableSubscriptionParams(s, prepStmt);
			//for subscription with id
			JdbcUtil.setParam(prepStmt, subscriptionId, 27, Types.INTEGER);			
			prepStmt.executeUpdate();
			
			retVal = findSubscription(s.getId());			
		}
		catch (SQLException se) {
			System.out.println("-------------------------- an SQLException occured");
			se.printStackTrace();
			throw new DAOException("Problem when attempting to update Subscription with id: " + subscriptionId, se);
		}
		catch(ValidationException ve){
			System.out.println("-------------------------- a ValidationException occured");
			ve.printStackTrace();
			throw new DAOException("Problem when attempting to update Subscription with id: " + subscriptionId, ve);
		}
		finally {
			JdbcUtil.cleanup(con, prepStmt, null);
			System.out.println(retVal+" ROWS AFFECTED -----------");
		}
		return retVal;
	}
	
//	@Override
//	public Subscription createSubscription(int id, String subscriptionNo,
//			Supplier supplier, PredefinedEntry status, float currentPrice,
//			float previousPrice, String internalLocation, LibraryLocation location,
//			PredefinedEntry kindOfAcquisition, Date orderingDate, Date startDate,
//			Date currentIssueDate, Date endDate, Date paidUntil,
//			String cancelPeriod, Boolean onlineInAleph, CostUnit cu,
//			Purchaser purch, BindingDetails bbdetails,
//			String sapAccPayableNo, String signature, String barcode,
//			String notes, String onlineNotes, PredefinedEntry currency,
//			PredefinedEntry kindOfStorage, Periodical per, int reminderFrequency) throws DAOException{
//		// TODO Auto-generated method stub
//		return null;
//	}
		
	public Subscription createSubscription() {
		return new Subscription();
	}
	
	public Subscription saveSubscription(Subscription subscription) throws DAOException{
		String sql = "INSERT INTO abos (id, abo_nr, abo_status, preis, preis_vorjahr," +
					 " abo_startdatum, abo_enddatum, interner_standort, bezahlt_bis, kuendigungsfrist," +
					 " online_in_aleph, signatur, bestellt_am, barcode, eingangsdatum_aktuelles_heft," +
					 " erwerbungsart, anmerkungen, anmerkungen_online, waehrung, aufbewahren," +
					 " bibliotheksstandorte_id, lieferanten_id, kostenstellen_id, besteller_id, zeitschriften_id," +
					 " bindedetails_id, mahnfrequenz_in_tagen)" +
					 " VALUES (?, ?, ?, ?, ?," +
					 " ?, ?, ?, ?, ?," +
					 " ?, ?, ?, ?, ?," +
					 " ?, ?, ?, ?, ?," +
					 " ?, ?, ?, ?, ?," +
					 " ?, ?)";
		Subscription createdSubscription = null;
		int subscriptionId = 0;
		Connection con = null;
		PreparedStatement prepStmt = null;
//		ResultSet rs = null;
		int rowsUpdated = 0;
		
		try {			
			con = getConProvider().getConnection();
			/* fetch next value of sequence from DB
			 * -> determine which DB to use and choose appropriate SQL 
			 */			
			Properties props = DAOFactoryJdbcImplBase.daoProperties;
			String sequenceSubscriptionIdSQL = props.getProperty("get-sequence-value.sql");
			System.out.println(sequenceSubscriptionIdSQL);
			PreparedStatement subscriptionIDStmt = con.prepareStatement(sequenceSubscriptionIdSQL);
			ResultSet subscrIdRs = subscriptionIDStmt.executeQuery();
			if (subscrIdRs.next()) {
				subscriptionId = subscrIdRs.getInt(1);
			}
			subscriptionIDStmt.close();
			System.out.println(subscriptionId);
			subscriptionIDStmt.close();
			prepStmt = con.prepareStatement(sql);
			prepStmt.setInt(1, subscriptionId);
			setUpdateableSubscriptionParams(subscription, prepStmt);
			rowsUpdated = prepStmt.executeUpdate();
			
//	not supported by Postgres JDBC Driver
//			rs = prepStmt.getGeneratedKeys();
//			
//			if (rs.next()){
//				createdSubscription = subscription;
//				createdSubscription.setId(rs.getInt(1));
//			}
			/* workaround: 
			 * use sequence value retrieved before INSERT-PrepStmt instead of PostgreSQL SERIAL data type 
			 */
			createdSubscription = findSubscription(subscriptionId);
		}
		catch (SQLException se) {
			throw new DAOException("Problem when attempting to save Subscription " + subscription, se);
		}
		finally {
			JdbcUtil.cleanup(con, prepStmt, null);
		}
		return createdSubscription;
	}
	
	private void setUpdateableSubscriptionParams(Subscription s, PreparedStatement prepStmt) throws SQLException{
		JdbcUtil.setParam(prepStmt, s.getSubscriptionNo(), 2, Types.VARCHAR);
		if (s.getStatus() != null)
			prepStmt.setInt(3, s.getStatus().getEntryId());
		else
			JdbcUtil.setParam(prepStmt, null, 3, Types.INTEGER);
		JdbcUtil.setParam(prepStmt, s.getCurrentPrice(), 4, Types.FLOAT);
		JdbcUtil.setParam(prepStmt, s.getPreviousPrice(), 5, Types.FLOAT);
		if (s.getStartDate() != null)
			prepStmt.setDate(6, new java.sql.Date(s.getStartDate().getTime()));
		else
			JdbcUtil.setParam(prepStmt, null, 6, Types.DATE);
		if (s.getEndDate() != null)
			prepStmt.setDate(7, new java.sql.Date(s.getEndDate().getTime()));
		else
			JdbcUtil.setParam(prepStmt, null, 7, Types.DATE);
		JdbcUtil.setParam(prepStmt, s.getInternalLocation(), 8, Types.VARCHAR);
		if (s.getPaidUntil() != null)
			prepStmt.setDate(9, new java.sql.Date(s.getPaidUntil().getTime()));
		else
			JdbcUtil.setParam(prepStmt, null, 9, Types.DATE);
		JdbcUtil.setParam(prepStmt, s.getCancellationPeriod(), 10, Types.VARCHAR);
		JdbcUtil.setParam(prepStmt, s.isOnlineInAleph(), 11, Types.BOOLEAN);
		JdbcUtil.setParam(prepStmt, s.getSignature(), 12, Types.VARCHAR);
		if (s.getOrderingDate() != null)
			prepStmt.setDate(13, new java.sql.Date(s.getOrderingDate().getTime()));
		else
			JdbcUtil.setParam(prepStmt, null, 13, Types.DATE);
		JdbcUtil.setParam(prepStmt, s.getBarcode(), 14, Types.VARCHAR);
		if (s.getCurrentIssueDate() != null)
			prepStmt.setDate(15, new java.sql.Date(s.getCurrentIssueDate().getTime()));
		else
			JdbcUtil.setParam(prepStmt, null, 15, Types.DATE);
		if (s.getKindOfAcquisition() != null)
			prepStmt.setInt(16, s.getKindOfAcquisition().getEntryId());
		else
			JdbcUtil.setParam(prepStmt, null, 16, Types.INTEGER);
		JdbcUtil.setParam(prepStmt, s.getNotes(), 17, Types.VARCHAR);
		JdbcUtil.setParam(prepStmt, s.getOnlineNotes(), 18, Types.VARCHAR);
		if (s.getCurrency() != null)
			prepStmt.setInt(19, s.getCurrency().getEntryId());
		else
			JdbcUtil.setParam(prepStmt, null, 19, Types.INTEGER);
		if (s.getKindOfStorage() != null)
			prepStmt.setInt(20, s.getKindOfStorage().getEntryId());
		else
			JdbcUtil.setParam(prepStmt, null, 20, Types.INTEGER);
		if (s.getLocation() != null)
			prepStmt.setInt(21, s.getLocation().getId());
		else
			JdbcUtil.setParam(prepStmt, null, 21, Types.INTEGER);
		if (s.getSupplier() != null)
			prepStmt.setInt(22, s.getSupplier().getId());
		else
			JdbcUtil.setParam(prepStmt, null, 22, Types.INTEGER);
		if (s.getCostUnit() != null) {
			JdbcUtil.setParam(prepStmt, s.getCostUnit().getId(), 23, Types.INTEGER);
		}
		else
		{
			JdbcUtil.setParam(prepStmt, null, 23, Types.INTEGER);
		}
		if (s.getPurchaser() != null)	
			prepStmt.setInt(24, s.getPurchaser().getId());
		else
			JdbcUtil.setParam(prepStmt, null, 24, Types.INTEGER);
		if (s.getPeriodical() != null)
			prepStmt.setInt(25, s.getPeriodical().getId());
		else
			JdbcUtil.setParam(prepStmt, null, 25, Types.INTEGER);
		if (s.getBookbindingDetails() != null)
			prepStmt.setInt(26, s.getBookbindingDetails().getId());
		else
			JdbcUtil.setParam(prepStmt, null, 26, Types.INTEGER);
		JdbcUtil.setParam(prepStmt, s.getReminderFrequency(), 27, Types.INTEGER);		
	}
	
	public void setSupplierDAO(SupplierDAO supplierDAO) {
		this.supplierDAO = supplierDAO;
	}
	public void setCostUnitDAO(CostUnitDAO costUnitDAO) {
		this.costUnitDAO = costUnitDAO;
	}
	public void setPurchaserDAO(PurchaserDAO purchaserDAO) {
		this.purchaserDAO = purchaserDAO;
	}
	public void setPeriodicalDAO(PeriodicalDAO periodicalDAO) {
		this.periodicalDAO = periodicalDAO;
	}
	public void setBindingDetailsDAO(BindingDetailsDAO bindingDetailsDAO) {
		this.bindingDetailsDAO = bindingDetailsDAO;
	}
	
	public void setPredefinedEntryDAO(PredefinedEntryDAO predefinedEntryDAO) {
		this.predefinedEntryDAO = predefinedEntryDAO;
	}

	public LibraryLocationDAO getLibraryLocationDAO() {
		return libraryLocationDAO;
	}

	public void setLibraryLocationDAO(LibraryLocationDAO libraryLocationDAO) {
		this.libraryLocationDAO = libraryLocationDAO;
	}
	/*TODO: auch Methoden bedenken, die ein SQL-Statement mit bestimmter
	* Sortierung vornehmen	* 
	*/

		
	
}
